package br.ucb.sigplan.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.ucb.sigplan.beans.Cronograma;
import br.ucb.sigplan.beans.PlanoEnsino;
import br.ucb.sigplan.beans.Turma;
import br.ucb.sigplan.connection.FactoryCon;
import br.ucb.sigplan.crud.CronogramaCrud;
import br.ucb.sigplan.dao.PlanoEnsinoDAO;
import br.ucb.sigplan.exception.DAOException;

public class PlanoEnsinoDAOImpl implements PlanoEnsinoDAO {
	private final String SQL_INSERT = "insert into planoEnsino (metodologia, recursos, articulacoes, avaliacoes, bibliografia, idturma) values (?,?,?, ?, ?, ?)";
	private final String SQL_FINDALL = "select * from planoEnsino p, turma t, disciplina d where p.idturma = t.idturma";
	private final String SQL_SEARCHBYID = "select * from planoEnsino p, turma t where p.idturma = t.idturma and p.idplanoEnsino = ?";
	private final String SQL_SEARCHBYTURMA = "select * from planoEnsino p, turma t, disciplina d where p.idturma = t.idturma and p.idturma = ?";
	private final String SQL_UPDATE = "update planoEnsino set metodologia = ?,recursos = ?,articulacoes = ?, avaliacoes = ?, bibliografia = ? where idplanoEnsino = ?";
	private final String SQL_DELETE = "delete from planoEnsino where idplanoEnsino = ?";
	
	public List<PlanoEnsino> findAll() throws DAOException {
		List<PlanoEnsino> planos = new ArrayList<PlanoEnsino>();
		Connection con = FactoryCon.getInstance().createConnection();
		PreparedStatement instrucao = null;
		ResultSet set = null;
		PlanoEnsino planoEnsino;
		try {
			instrucao = con.prepareStatement(SQL_FINDALL);
			set = instrucao.executeQuery();
			while (set.next()) {
				planoEnsino = new PlanoEnsino();
				putPlano(set, planoEnsino);
				planos.add(planoEnsino);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException("erroPesquisa");
		} finally {
			FactoryCon.closeConnection(con, instrucao, set);
		}
		return planos;
	}
	public PlanoEnsino findPlanoEnsinoById(Integer idensinoPlano) throws DAOException {
		Connection con = FactoryCon.getInstance().createConnection();
		PreparedStatement instrucao = null;
		ResultSet set = null;
		PlanoEnsino planoEnsino = null;
		try {
			instrucao = con.prepareStatement(SQL_SEARCHBYID);
			instrucao.setInt(1, idensinoPlano);
			set = instrucao.executeQuery();
			while (set.next()) {
				planoEnsino = new PlanoEnsino();
				putPlano(set, planoEnsino);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException("erroPesquisa");
		} finally {
			FactoryCon.closeConnection(con, instrucao, set);
		}
		return planoEnsino;
	}
	
	public void insert(PlanoEnsino planoEnsino) throws DAOException {
		Connection con = FactoryCon.getInstance().createConnection();
		PreparedStatement instrucao = null;
		try {
			instrucao = con.prepareStatement(SQL_INSERT);
			instrucao.setString(1, planoEnsino.getMetodologia());
			instrucao.setString(2, planoEnsino.getRecursos());
			instrucao.setString(3, planoEnsino.getArticulacoes());
			instrucao.setString(4, planoEnsino.getAvaliacoes());
			instrucao.setString(5, planoEnsino.getBibliografia());
			instrucao.setInt(6, planoEnsino.getTurma().getIdTurma());
			instrucao.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException("erroInsercao");
		} finally {
			FactoryCon.closeConnection(con, instrucao);
		}
	}
	
	private void putPlano(ResultSet set, PlanoEnsino planoEnsino) throws SQLException {
		planoEnsino.setIdPlano(set.getInt("idplanoEnsino"));
		planoEnsino.setMetodologia(set.getString("metodologia"));
		planoEnsino.setRecursos(set.getString("recursos"));
		planoEnsino.setArticulacoes(set.getString("articulacoes"));
		planoEnsino.setAvaliacoes(set.getString("avaliacoes"));
		planoEnsino.setBibliografia(set.getString("bibliografia"));
		planoEnsino.setTurma(getTurma(set));
		planoEnsino.setCronograma(getCronograma(set).getCronograma().entrySet());
	}
	
	private Turma getTurma(ResultSet set) throws SQLException {
		Turma turma = new Turma();
		new TurmaDAOImpl().putTurma(set, turma);
		return turma;
	}
	
	private Cronograma getCronograma(ResultSet set) throws SQLException{
		 return CronogramaCrud.searchByPlano(set.getInt("idplanoEnsino"));
	}
	
	public void updatePlanoById(PlanoEnsino plano) throws DAOException {
		Connection con = FactoryCon.getInstance().createConnection();
		PreparedStatement instrucao = null;
		try {
			instrucao = con.prepareStatement(SQL_UPDATE);
			instrucao.setString(1, plano.getMetodologia());
			instrucao.setString(2, plano.getRecursos());
			instrucao.setString(3, plano.getArticulacoes());
			instrucao.setString(4, plano.getAvaliacoes());
			instrucao.setString(5, plano.getBibliografia());
			instrucao.setInt(6, plano.getIdPlano());
			instrucao.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException("erroAtualizacao");
		} finally {
			FactoryCon.closeConnection(con, instrucao);
		}
	}
	public void delete(Integer idplano) throws DAOException {
		Connection con = FactoryCon.getInstance().createConnection();
		PreparedStatement instrucao = null;
		try {
			instrucao = con.prepareStatement(SQL_DELETE);
			instrucao.setInt(1, idplano);
			instrucao.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException("planoDelecao");
		} finally {
			FactoryCon.closeConnection(con, instrucao);
		}
		
	}
	public PlanoEnsino findByTurma(Turma turma) throws DAOException {
		Connection con = FactoryCon.getInstance().createConnection();
		PlanoEnsino planoEnsino = null;
		PreparedStatement instrucao = null;
		ResultSet set = null;
		try {
			instrucao = con.prepareStatement(SQL_SEARCHBYTURMA);
			instrucao.setInt(1, turma.getIdTurma());
			set = instrucao.executeQuery();
			while (set.next()) {
				planoEnsino = new PlanoEnsino();
				putPlano(set, planoEnsino);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException("erroPesquisa");
		} finally {
			FactoryCon.closeConnection(con, instrucao, set);
		}
		return planoEnsino;
	}
}
